

CREATE PROCEDURE [dbo].[STIGetInfo] @FactorId DECIMAL(20, 0)
AS
-----------------------------------------HEADER-------------------------------------

DECLARE @taxid VARCHAR(MAX) = '';
DECLARE @bbc VARCHAR(MAX) = '';
DECLARE @bid VARCHAR(MAX) = '0';
DECLARE @billid VARCHAR(MAX) = '0';
DECLARE @bpc VARCHAR(MAX) = '0';
DECLARE @bpn VARCHAR(MAX) = '0';
DECLARE @crn VARCHAR(MAX) = '0';
DECLARE @ft INT = 0;
DECLARE @irinno VARCHAR(MAX) = '0';
DECLARE @inp INT = 1;
DECLARE @ins INT = 1;
DECLARE @inty INT = 2;
DECLARE @irtaxid VARCHAR(MAX) = '';
DECLARE @sbc VARCHAR(MAX) = '0';
DECLARE @scc VARCHAR(MAX) = '0';
DECLARE @scln VARCHAR(MAX) = '0';
DECLARE @tax17 VARCHAR(MAX) = NULL;
DECLARE @tdis INT = 0;
DECLARE @tinb VARCHAR(MAX) = '0';
DECLARE @tins VARCHAR(MAX);
DECLARE @tob INT = 1;
DECLARE @todam INT = 0;
DECLARE @tvop DECIMAL(20, 0) = 0;
DECLARE @dpvb INT = 0;

DECLARE @TechnicalAmount MONEY = 0;

------------------------------------------------BODY------------------------------------------
DECLARE @bros VARCHAR(MAX) = '0';
DECLARE @bsrn VARCHAR(MAX) = NULL;
DECLARE @cfee VARCHAR(MAX) = '0';
DECLARE @consfee VARCHAR(MAX) = '0';
DECLARE @cop VARCHAR(MAX) = '0';
DECLARE @cut VARCHAR(MAX) = '0';
DECLARE @dis VARCHAR(MAX) = '0';
DECLARE @exr VARCHAR(MAX) = '0';
DECLARE @odam VARCHAR(MAX) = '0';
DECLARE @odr VARCHAR(MAX) = '0';
DECLARE @odt VARCHAR(MAX) = '0';
DECLARE @olam VARCHAR(MAX) = '0';
DECLARE @olr VARCHAR(MAX) = '0';
DECLARE @olt VARCHAR(MAX) = '0';
DECLARE @spro VARCHAR(MAX) = '0';
DECLARE @tcpbs VARCHAR(MAX) = '0';
DECLARE @vop DECIMAL(20, 0) = 0;
DECLARE @vra TINYINT;


------------------  Calc Technical Amount
SELECT @TechnicalAmount = ISNULL(H.HOtc, 0) + ISNULL(H.TotPriceFaniSazman, 0) + ISNULL(H.TotPriceFaniBimar, 0)
FROM dbo.DrugH H
WHERE H.Id_Havaleh = @FactorId;

------------------  Calc Tax Percent 
SELECT @vra = SUM(Max_Percent)
FROM dbo.Over_Under
WHERE Action_Code IN ( '-4', '-5' )
      AND Active = 1
      AND Type = 1;

------------------  Calc Sum of detail data
DECLARE @SumOfDetail MONEY = 0 
DECLARE @SumOfTax MONEY = 0 

SELECT @SumOfDetail = @TechnicalAmount + SUM(FLOOR(
CASE
           WHEN ISNULL(G.Duty_Flag, 0) = 1 THEN
               D.Tot_forosh + ISNULL(D.Tot_Differ, 0) + ((D.Tot_forosh +ISNULL(D.Tot_Differ, 0)) * (.01 * @vra))
           ELSE
               D.Tot_forosh + ISNULL(D.Tot_Differ, 0)
       END) )  ,
	   @SumOfTax = FLOOR(SUM (CASE
                          WHEN ISNULL(G.Duty_Flag, 0) = 1 THEN
                      floor(((ISNULL(D.Tot_Differ, 0) + D.Tot_forosh) * (.01 * @vra) ) )
                          ELSE
                              0
                      END))
          
FROM dbo.DrugHavaleh D
 JOIN dbo.KalaId G
        ON G.K_Code = D.K_Code
WHERE D.Id_Havaleh = @FactorId
AND D.Status <> 8

--SELECT 

--@SumOfTax = SUM (CASE
--                          WHEN ISNULL(G.Duty_Flag, 0) = 1 THEN
--                      floor(ROUND((ISNULL(D.Tot_Differ, 0) + D.Tot_forosh) * (.01 * @vra) , 0) )
--                          ELSE
--                              0
--                      END)
--FROM dbo.DrugHavaleh D
-- JOIN dbo.KalaId G
--        ON G.K_Code = D.K_Code
--WHERE D.Id_Havaleh = @FactorId
---- Coopration Info From Manager Database

SELECT @tins = CodeEghtesadi
FROM Manager.dbo.Cooprations;


------

SELECT @taxid taxid,
       @bbc bbc,
       @bid bid,
       @billid billid,
       @bpc bpc,
       @bpn bpn,
       ISNULL(H.TotPriceDaryafti, 0) cap,
       @crn crn,
       @dpvb dpvb,
       @ft ft,
       dbo.UNIX_TIMESTAMP(H.Tarikh) indatim,
       dbo.UNIX_TIMESTAMP(H.Tarikh) indati2m,
       CAST(CASE
                WHEN Kind_Flag = 2 THEN
                    H.Nu_Bargasht_Forosh
                ELSE
                    CAST(H.Nu_Forosh AS VARCHAR(10)) + '44169'
            END AS VARCHAR(20)) inno,
       @irinno irinno,
       @inp inp,
       @ins ins,
       insp = CASE
                  WHEN (ISNULL(H.TotPriceDaryafti, 0) = 0)
                       OR
                       (
                           ISNULL(H.TotPriceSazman, 0) > 0
                           AND ISNULL(H.TotPriceDaryafti, 0) > 0
                       )
                       OR (ISNULL(OU.SupportivePrice, 0) > 0) THEN
                      ISNULL(H.TotPriceDiffer, 0) + ISNULL(H.TotPriceAzad, 0) + ISNULL(H.TotPriceKala, 0)
                      + ISNULL(H.TotPriceOTC, 0) + ISNULL(H.TotPriceBimar, 0) + ISNULL(H.TotPriceSazman, 0)
                      + ISNULL(H.TotPriceFaniBimar, 0) + ISNULL(H.HOtc, 0) + ISNULL(H.TotPriceFaniSazman, 0)
                      + ISNULL(@SumOfTax, 0) - ISNULL(H.TotPriceDaryafti, 0)
                  ELSE
                      0
              END,
       @inty inty,
       @irtaxid irtaxid,
       @sbc sbc,
       @scc scc,
       @scln scln,
       setm = CASE
                  WHEN (
                           ISNULL(H.TotPriceSazman, 0) > 0
                           OR ISNULL(OU.SupportivePrice, 0) > 0
                       )
                       AND ISNULL(H.TotPriceDaryafti, 0) > 0 THEN
                      3
                  WHEN ISNULL(H.TotPriceDaryafti, 0) = 0 THEN
                      2
                  ELSE
                      1
              END,
       --ISNULL(H.TotPriceDiffer, 0) + ISNULL(H.TotPriceAzad, 0) + ISNULL(H.TotPriceKala, 0) + ISNULL(H.TotPriceOTC, 0)
       --+ ISNULL(H.TotPriceBimar, 0) + ISNULL(H.TotPriceSazman, 0) + ISNULL(H.TotPriceFaniBimar, 0) + ISNULL(H.HOtc, 0)
       --+ ISNULL(H.TotPriceFaniSazman, 0) tadis,
	   @SumOfDetail - ISNULL(@SumOfTax, 0) tadis,
	   
       @tax17 tax17,
       --ISNULL(H.TotPriceDiffer, 0) + ISNULL(H.TotPriceAzad, 0) + ISNULL(H.TotPriceKala, 0) + ISNULL(H.TotPriceOTC, 0)
       --+ ISNULL(H.TotPriceBimar, 0) + ISNULL(H.TotPriceSazman, 0) + ISNULL(H.TotPriceFaniBimar, 0) + ISNULL(H.HOtc, 0)
       --+ ISNULL(H.TotPriceFaniSazman, 0) + ISNULL(OU.TaxPrice, 0) tbill,
	   @SumOfDetail tbill,
       @tdis tdis,
       @tinb tinb,
       @tins tins,
       @tob tob,
       @todam todam,
       --ISNULL(H.TotPriceDiffer, 0) + ISNULL(H.TotPriceAzad, 0) + ISNULL(H.TotPriceKala, 0) + ISNULL(H.TotPriceOTC, 0)
       --+ ISNULL(H.TotPriceBimar, 0) + ISNULL(H.TotPriceSazman, 0) + ISNULL(H.TotPriceFaniBimar, 0) + ISNULL(H.HOtc, 0)
       --+ ISNULL(H.TotPriceFaniSazman, 0) tprdis,
	   @SumOfDetail - ISNULL(@SumOfTax, 0) tprdis,
       --ISNULL(OU.TaxPrice, 0) tvam,
	   (ISNULL(@SumOfTax,0)) tvam,
       @tvop tvop
FROM dbo.DrugH AS H
    LEFT OUTER JOIN
    (
        SELECT CardPrice = SUM(   CASE
                                      WHEN Guy_Code = -4 THEN
                                          Price
                                      ELSE
                                          0
                                  END
                              ),
         --      TaxPrice = SUM(  CASE
         --                            WHEN Guy_Code = -5 THEN
									--( Price)
         --                            ELSE
         --                                0
         --                        END
         --                    ),
               SupportivePrice = SUM(   CASE
                                            WHEN Guy_Code = -3 THEN
                                                Price
                                            ELSE
                                                0
                                        END
                                    ),
               Id_Havaleh
        FROM dbo.Over_Under_Factor_Drug
        WHERE Guy_Code IN ( '-3', '-4', '-5' )
        GROUP BY Id_Havaleh
    ) AS OU
        ON OU.Id_Havaleh = H.Id_Havaleh
WHERE H.Id_Havaleh = @FactorId;
-------------------------
SELECT D.K_Qty1 am,
       @bros bros,
       @bsrn bsrn,
       @cfee cfee,
       @consfee consfee,
       @cop cop,
       @cut cut,
       @dis dis,
       @exr exr,	   
       CASE
           WHEN D.Status IN ( 4, 6, 7,9 )
                AND ISNULL(D.Tot_Differ, 0) > 0 THEN
       (D.Tot_Differ / D.K_Qty1) + D.Price_Forosh
           ELSE
               D.Price_Forosh
       END fee,
       CAST(ISNULL(UM.TaxCode, '1627') AS VARCHAR(10)) mu,
       @odam odam,
       @odr odr,
       @odt odt,
       @olam olam,
       @olr olr,
       @olt olt,
       ISNULL(D.Tot_Differ, 0) + D.Tot_forosh prdis,
       ISNULL(D.Tot_Differ, 0) + D.Tot_forosh adis,
       @spro spro,
       ISNULL(GM.TaxCode, STUFF(REPLICATE('0', 13), 14 - LEN(G.K_Code), LEN(G.K_Code), G.K_Code)) sstid,
       CASE
           WHEN ISNULL(GM.TaxName, '') = '' THEN
               G.Name1
           ELSE
               GM.TaxName
       END AS sstt,
       @tcpbs tcpbs,
       CASE
           WHEN ISNULL(G.Duty_Flag, 0) = 1 THEN
               FLOOR(D.Tot_forosh + ISNULL(D.Tot_Differ, 0) + (D.Tot_forosh + +ISNULL(D.Tot_Differ, 0)) * (.01 * @vra))
           ELSE
               FLOOR(D.Tot_forosh + ISNULL(D.Tot_Differ, 0))
       END tsstam,
       vam =   CASE
                          WHEN ISNULL(G.Duty_Flag, 0) = 1 THEN
                      FLOOR((ISNULL(D.Tot_Differ, 0) + D.Tot_forosh) * (.01 * @vra))
                          ELSE
                              0
                      END
                  ,
       @vop vop,
       CASE
           WHEN ISNULL(G.Duty_Flag, 0) = 1 THEN
               CAST(@vra AS VARCHAR(MAX))
           ELSE
               0
       END vra
FROM dbo.DrugHavaleh D
    JOIN dbo.KalaId G
        ON G.K_Code = D.K_Code
    LEFT JOIN dbo.STIGoodsMapping GM
        ON GM.GoodsCode = G.K_Code
    LEFT JOIN dbo.STIUnitMaping UM
        ON UM.UnitCode = G.U_Code1
WHERE D.Id_Havaleh = @FactorId
      AND D.Status <> 8
UNION ALL
SELECT 1 am,
       @bros bros,
       @bsrn bsrn,
       @cfee cfee,
       @consfee consfee,
       @cop cop,
       @cut cut,
       @dis dis,
       @exr exr,
       @TechnicalAmount fee,
       '1627' mu,
       @odam odam,
       @odr odr,
       @odt odt,
       @olam olam,
       @olr olr,
       @olt olt,
       @TechnicalAmount prdis,
       @TechnicalAmount adis,
       @spro spro,
       '5555555555555' sstid,
       'حق فنی' sstt,
       @tcpbs tcpbs,
       @TechnicalAmount tsstam,
       0 vam,
       @vop vop,
       0 vra
WHERE @TechnicalAmount > 0;

-------------------------------------------PAYMENTS---------------------------------------------
SELECT '0' acn,
       '0' iinn,
       '0' pcn,
       '0' pdt,
       '0' pid,
       '0' trmn,
       '0' trn;


